package com.hp.jus.core.impl;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class JdbcJusImpl extends JusImpl{
	
	private String JNDIname;
	private String driver;
	private String host;
	private String port;
	private String username;
	private String password;
	private String database;
	private String table;
	private String keycolumn;
	private String urlcolumn;
	private String dburl;
	private Connection con;
	private PreparedStatement addPpst;
	private PreparedStatement removePpst;
	private PreparedStatement updatePpst;
	private PreparedStatement getPpst;
	private String addSql;
	private String removeSql;
	private String updateSql;
	private String getSql;

	@Override
	public Boolean add(String key, URL url)throws Exception {
		initConnection();
		addPpst.setString(1, key);
		addPpst.setString(2, url.toString());
		addPpst.execute();
		int affectedRows = addPpst.getUpdateCount();
		return affectedRows>0;
	}

	@Override
	public Boolean remove(String key)throws Exception {
		initConnection();
		removePpst.setString(1, key);
		removePpst.execute();
		int affectedRows = removePpst.getUpdateCount();
		return affectedRows>0;
	}

	@Override
	public Boolean update(String key, URL url)throws Exception {
		initConnection();
		updatePpst.setString(1, url.toString());
		updatePpst.setString(2, key);
		updatePpst.execute();
		int affectedRows = updatePpst.getUpdateCount();
		return affectedRows>0;
	}

	@Override
	public URL get(String key)throws Exception {
		initConnection();
		URL url = null;
		getPpst.setString(1, key);
		ResultSet rs = getPpst.executeQuery();
		if(rs.next())
			url = new URL(rs.getString(1));
		return url;
	}
	
	private Connection getConnection()throws Exception{
		if(JNDIname!=null&&JNDIname.length()>0){
			Context ctx = new InitialContext();
			DataSource ds = (DataSource)ctx.lookup(JNDIname);
			return ds.getConnection();
		}else{
			Class.forName(driver);
			return DriverManager.getConnection(getDBurl(), username, password);
		}
	}

	private String getDBurl() {
		return dburl.replace("{impl.class.jdbc.host}", host).replace("{impl.class.jdbc.port}", port).replace("{impl.class.jdbc.username}", username)
		.replace("{impl.class.jdbc.password}", password).replace("{impl.class.jdbc.database}", database);
	}

	@Override
	public void init()throws Exception {
		JNDIname = props.getProperty("impl.class.jdbc.JNDIname");
		driver = props.getProperty("impl.class.jdbc.driver");
		host = props.getProperty("impl.class.jdbc.host");
		port = props.getProperty("impl.class.jdbc.port");
		username = props.getProperty("impl.class.jdbc.username");
		password = props.getProperty("impl.class.jdbc.password");
		database = props.getProperty("impl.class.jdbc.database");
		table = props.getProperty("impl.class.jdbc.table");
		keycolumn = props.getProperty("impl.class.jdbc.keycolumn");
		urlcolumn = props.getProperty("impl.class.jdbc.urlcolumn");
		dburl = props.getProperty("impl.class.jdbc.url");
		addSql = "INSERT INTO "+table+" ("+keycolumn+", "+urlcolumn+") VALUES(?, ?)";
		removeSql = "DELETE FROM "+table+" WHERE "+keycolumn+" = ?";
		updateSql = "UPDATE "+table+" SET "+urlcolumn+"=? WHERE "+keycolumn+" = ?";
		getSql = "SELECT "+urlcolumn+" FROM "+table+" WHERE "+keycolumn+" = ?";
		initConnection();
		addPpst = con.prepareStatement(addSql);
		removePpst = con.prepareStatement(removeSql);
		updatePpst = con.prepareStatement(updateSql);
		getPpst = con.prepareStatement(getSql);
	}

	private void initConnection()throws Exception {
		if(con==null||con.isClosed())
			con = getConnection();
	}

	@Override
	public void close()throws Exception {
		getPpst.close();
		removePpst.close();
		updatePpst.close();
		addPpst.close();
		con.close();
	}

}
